# -*- coding: utf-8 -*-

# Define here the models for your scraped items
#
# See documentation in:
# https://doc.scrapy.org/en/latest/topics/items.html

import scrapy


class CrawlItem(scrapy.Item):
    # define the fields for your item here like:
    # name = scrapy.Field()
    pass

# 图片以及文字文章
class Articleitem(scrapy.Item):
    zhujian = 'title'
    table = "cra_technique_article"
    # 标题
    title = scrapy.Field()
    # 发布日期
    publish_time = scrapy.Field()
    # 文章原始源
    origin = scrapy.Field()
    # 爬虫获取源
    source = scrapy.Field()
    # 文章链接
    url = scrapy.Field()
    # 文章类型
    type = scrapy.Field()
    # 作者
    author = scrapy.Field()
    # 摘要或简介
    abstract = scrapy.Field()
    # 正文内容
    content = scrapy.Field()
    # 正文图片本地保存目录
    picture_loc = scrapy.Field()
    # 视频新闻在线链接
    video_url = scrapy.Field()
    # 视频本地保存目录
    video_loc = scrapy.Field()
    # 备注
    mark = scrapy.Field()

    # TODO(liujunfeng)：额外的信息，可以考虑写进数据库
    # 匹配到的关键词
    keywords = scrapy.Field()
    # 文章内图片 url
    images = scrapy.Field()

    # form the insert sql command
    @staticmethod
    def insert_command(item, cur_time, update_time, delete_time):
        sql = 'INSERT into cra_technique_article(title, publish_time, source, origin, url, type, '\
              'author, abstract, content, picture_loc, video_url, video_loc, create_at, update_at, delete_at, mark) '\
              'VALUES ("{}", "{}", "{}", "{}", "{}", "{}", "{}", "{}", "{}", "{}", "{}", "{}", "{}", "{}", "{}", "{}")'\
            .format(item['title'], item.get('publish_time', None), item.get('source', None), item.get('origin', None), item.get('url', None),
                    item.get('type', None), item.get('author', None), item.get('abstract', None), item.get('content', None), item.get('picture_loc', None),
                    item.get('video_url', None), item.get('video_loc', None), cur_time, cur_time, cur_time, item.get('mark', None))
        return sql


class Gongsiitem(scrapy.Item):
    zhujian = 'name'
    table = "cra_organization"
    name = scrapy.Field()
    name_en = scrapy.Field()
    department = scrapy.Field()
    nation = scrapy.Field()
    address = scrapy.Field()
    telphone = scrapy.Field()
    email = scrapy.Field()
    fax = scrapy.Field()
    description = scrapy.Field()
    coordinate = scrapy.Field()
    mark = scrapy.Field()

    # form the insert sql command
    @staticmethod
    def insert_command(item, cur_time, update_time, delete_time):
        sql = 'INSERT into cra_organization(name, name_en, department, nation, address, telphone, '\
              'email, fax, description, coordinate, create_at, update_at, delete_at, is_deleted, mark) '\
              'VALUES ("{}", "{}", "{}", "{}", "{}", "{}", "{}", "{}", "{}", "{}", "{}", "{}", "{}", "{}", "{}")'\
            .format(item.get('name', None), item.get('name_en', None), item.get('department', None), item.get('nation', None), item.get('address', None),
                    item.get('telphone', None), item.get('applicant', None), item.get('fax', None), item.get('description', None), item.get('coordinate', None),
                    cur_time, cur_time, cur_time, 0, None).replace('"None"', 'NULL')
        return sql

    @staticmethod
    def update_command(item, name, cur_time, update_time, delete_time):
        sql = 'UPDATE cra_organization SET ' \
              'name = "{}", name_en = "{}", department = "{}", nation = "{}", address = "{}",' \
              'telphone = "{}", email = "{}", fax = "{}", description = "{}",' \
              'coordinate = "{}", update_at = "{}", is_deleted = "{}", mark = "{}" ' \
              'WHERE name = "{}"'\
            .format(item.get('name', None), item.get('name_en', None), item.get('department', None), item.get('nation', None), item.get('address', None),
                    item.get('telphone', None), item.get('email', None), item.get('fax', None), item.get('description', None), item.get('coordinate', None),
                    cur_time, 0, None, name).replace('"None"', 'NULL')
        return sql

class Authoritem(scrapy.Item):
    zhujian = 'name'
    table = "cra_author"
    name = scrapy.Field()
    normalized_name = scrapy.Field()
    title = scrapy.Field()
    email_list = scrapy.Field()
    email = scrapy.Field()
    num_pubs = scrapy.Field()
    num_citation = scrapy.Field()
    h_index = scrapy.Field()
    research_direction = scrapy.Field()
    mark = scrapy.Field()

    # form the insert sql command
    @staticmethod
    def insert_command(item, cur_time, update_time, delete_time):
        sql = 'INSERT into cra_author(name, normalized_name, title, email_list, email, num_pubs, '\
              'num_citation, h_index, research_direction, create_at, update_at, delete_at, is_deleted, mark) '\
              'VALUES ("{}", "{}", "{}", "{}", "{}", "{}", "{}", "{}", "{}", "{}", "{}", "{}", "{}", "{}")'\
            .format(item.get('name', None), item.get('normalized_name', None), item.get('title', None), item.get('email_list', None), item.get('email', None),
                    item.get('num_pubs', None), item.get('num_citation', None), item.get('h_index', None), item.get('research_direction', None),
                    cur_time, cur_time, cur_time, 0, None).replace('"None"', 'NULL')
        return sql

    @staticmethod
    def update_command(item, name, cur_time, update_time, delete_time):
        sql = 'UPDATE cra_author SET ' \
              'name = "{}", normalized_name = "{}", title = "{}", email_list = "{}", email = "{}",' \
              'num_pubs = "{}", num_citation = "{}", h_index = "{}", research_direction = "{}",' \
              'update_at = "{}", is_deleted = "{}", mark = "{}" ' \
              'WHERE name = "{}"'\
            .format(item.get('name', None), item.get('normalized_name', None), item.get('title', None), item.get('email_list', None), item.get('email', None),
                    item.get('num_pubs', None), item.get('num_citation', None), item.get('h_index', None), item.get('research_direction', None),
                    cur_time,  0, None, name).replace('"None"', 'NULL')
        return sql

class Zlfitem(scrapy.Item):
    zhujian = 'title'
    table = "cra_patent"
    # pub_number 专利授权号 //div[@id='questelTableViewItem_{}']//td[@fieldname='MyPN']//text()
    keyword = scrapy.Field()
    pub_number = scrapy.Field()
    app_number = scrapy.Field()
    app_year = scrapy.Field()
    type = scrapy.Field()
    status = scrapy.Field()
    simple_status = scrapy.Field()
    applicant = scrapy.Field()
    source_id = scrapy.Field()
    proposer = scrapy.Field()
    cur_applicant = scrapy.Field()
    title = scrapy.Field()
    ipc = scrapy.Field()
    app_time = scrapy.Field()
    open_year = scrapy.Field()
    open_time = scrapy.Field()
    inpa_doc = scrapy.Field()
    legal_status_time = scrapy.Field()
    inventors = scrapy.Field()
    first_inventor = scrapy.Field()
    inventors_count = scrapy.Field()
    applicant_count = scrapy.Field()
    applicant_province = scrapy.Field()
    inpa_doc_count = scrapy.Field()
    patent_count = scrapy.Field()
    inpa_doc_patent = scrapy.Field()
    patent_family = scrapy.Field()
    ipc_main_number = scrapy.Field()
    priority_country = scrapy.Field()
    priority_number = scrapy.Field()
    priority_date = scrapy.Field()
    mark = scrapy.Field()

class Paperitem(scrapy.Item):
    zhujian = 'title'
    table = 'cra_paper'
    title = scrapy.Field()
    keyword = scrapy.Field()
    author = scrapy.Field()
    author_url = scrapy.Field()

    org = scrapy.Field()
    org_url = scrapy.Field()

    conf_paper = scrapy.Field()
    conf_name = scrapy.Field()
    conf_date = scrapy.Field()
    conf_addr = scrapy.Field()
    conf_org = scrapy.Field()

    abstract = scrapy.Field()

    keywords = scrapy.Field()

    cate = scrapy.Field()

class Patentitem(scrapy.Item):
    zhujian = 'title'
    table = "cra_patent"
    # pub_number 专利授权号 //div[@id='questelTableViewItem_{}']//td[@fieldname='MyPN']//text()
    keyword = scrapy.Field()
    pub_number = scrapy.Field()
    app_number = scrapy.Field()
    app_year = scrapy.Field()
    type = scrapy.Field()
    status = scrapy.Field()
    simple_status = scrapy.Field()
    applicant = scrapy.Field()
    source_id = scrapy.Field()
    proposer = scrapy.Field()
    cur_applicant = scrapy.Field()
    title = scrapy.Field()
    ipc = scrapy.Field()
    app_time = scrapy.Field()
    open_year = scrapy.Field()
    open_time = scrapy.Field()
    inpa_doc = scrapy.Field()
    legal_status_time = scrapy.Field()
    inventors = scrapy.Field()
    first_inventor = scrapy.Field()
    inventors_count = scrapy.Field()
    applicant_count = scrapy.Field()
    applicant_province = scrapy.Field()
    inpa_doc_count = scrapy.Field()
    patent_count = scrapy.Field()
    inpa_doc_patent = scrapy.Field()
    patent_family = scrapy.Field()
    ipc_main_number = scrapy.Field()
    priority_country = scrapy.Field()
    priority_number = scrapy.Field()
    priority_date = scrapy.Field()
    mark = scrapy.Field()
    domain = scrapy.Field()
    
    # app_number 申请号 //span[contains(text(), 'Application number')]/following-sibling::span[1]
    # app_year 申请年 //span[contains(text(), '日期')]/following-sibling::span[1]
    # type 专利类型
    # status 有效状态
    # simple_status 专利状态简洁名称 //div[@id='PatentTreeGrid']//div[@class='x-grid3-row '][2]//td[2]
    # applicant 申请人/申请单位
    # source_id 专利数据来源
    # proposer 提名？
    # cur_applicant 当前申请人？
    # title 专利名称
    # ipc IPC分类号
    # app_time 申请时间 //span[contains(text(), '日期')]/following-sibling::span[1]
    # open_year 开放年 //div[@id='PatentTreeGrid']//div[@class='x-grid3-row '][2]//td[2]
    # open_time 开放日期 //div[@id='PatentTreeGrid']//div[@class='x-grid3-row '][2]//td[2]
    # inpa_doc INPA DOC编码
    # legal_status_time 生效时间 //ul[@class='hierarchical-tree--dotted']//span[@class='gwt-InlineLabel publicationList-Publication-Date']
    # inventors 发明人 //div//td[contains(text(), 'Inventor')]/following-sibling::td
    # first_inventor 第一发明人
    # inventors_count 发明人数
    # applicant_count 申请人数
    # applicant_province 申请省份
    # inpa_doc_count INPA DOC计数？
    # patent_count 专利计数？

    # form the insert sql command
    @staticmethod
    def insert_command(item, cur_time, update_time, delete_time):
        sql = 'INSERT into cra_patent(pub_number, app_number, app_year, type, status, simple_status, applicant, source_id, proposer, cur_applicant,title, ipc, app_time, open_year, open_time, inpa_doc, legal_status_time, inventors, first_inventor, inventors_count, applicant_count, applicant_province, inpa_doc_count, patent_count, inpa_doc_patent, patent_family, ipc_main_number, priority_country, priority_number, priority_date, create_at, update_at, delete_at, is_deleted, mark, technology_domain) VALUES ("{}", "{}", "{}", "{}", "{}", "{}", "{}", "{}", "{}", "{}", "{}", "{}", "{}", "{}", "{}", "{}", "{}", "{}", "{}", "{}", "{}", "{}", "{}", "{}", "{}", "{}", "{}", "{}", "{}", "{}", "{}", "{}", "{}", "{}", "{}", "{}")'\
            .format(item.get('pub_number', None), item.get('app_number', None), item.get('app_year', None), item.get('type', None), item.get('status', None),
                    item.get('simple_status', None), item.get('applicant', None), item.get('source_id', None), item.get('proposer', None), item.get('cur_applicant', None),
                    item.get('title', None), item.get('ipc', None), item.get('app_time', cur_time),item.get('open_year', None), item.get('open_time', cur_time),
                    item.get('inpa_doc', None), item.get('legal_status_time', cur_time), item.get('inventors', None), item.get('first_inventor', None),
                    item.get('inventors_count', None),item.get('applicant_count', None), item.get('applicant_province', None),item.get('inpa_doc_count', None),
                    item.get('patent_count', None),item.get('patent_family', None), item.get('ipc_main_number', None),
                    item.get('priority_country', None), item.get('priority_number', None),item.get('priority_date', None), item.get('patent_count', None), cur_time, cur_time, cur_time, None, item.get('mark', None), item.get('domain'), None).replace('"None"', 'NULL')
        return sql

    @staticmethod
    def update_command(item, title, cur_time, update_time, delete_time):
        sql = 'UPDATE cra_patent SET ' \
              'pub_number = "{}", app_number = "{}", app_year = "{}", type = "{}", status = "{}", simple_status = "{}", applicant = "{}", ' \
              'source_id = "{}", proposer = "{}", cur_applicant = "{}", title = "{}", ipc = "{}", app_time = "{}", open_year = "{}", ' \
              'open_time = "{}", inpa_doc = "{}", legal_status_time = "{}", inventors = "{}", first_inventor = "{}", inventors_count = "{}", applicant_count = "{}", ' \
              'applicant_province = "{}", inpa_doc_count = "{}", patent_count = "{}", inpa_doc_patent = "{}", patent_family = "{}", ipc_main_number = "{}", priority_country = "{}", ' \
              'priority_number = "{}", priority_date = "{}", update_at = "{}", is_deleted = "{}", mark = "{}", technology_domain = "{}"' \
              'WHERE title = "{}"'\
            .format(item.get('pub_number', None), item.get('app_number', None), item.get('app_year', None), item.get('type', None), item.get('status', None),
                    item.get('simple_status', None), item.get('applicant', None), item.get('source_id', None), item.get('proposer', None), item.get('cur_applicant', None),
                    item.get('title', None), item.get('ipc', None), item.get('app_time', cur_time),item.get('open_year', None), item.get('open_time', cur_time),
                    item.get('inpa_doc', None), item.get('legal_status_time', cur_time), item.get('inventors', None), item.get('first_inventor', None),
                    item.get('inventors_count', None),item.get('applicant_count', None), item.get('applicant_province', None),item.get('inpa_doc_count', None),
                    item.get('patent_count', None),item.get('patent_family', None), item.get('ipc_main_number', None),
                    item.get('priority_country', None), item.get('priority_number', None),item.get('priority_date', None), item.get('patent_count', None), cur_time, None, item.get('mark', None), item.get('domain', None), title).replace('"None"', 'NULL')
        return sql

class Author_gongsi(scrapy.Item):
    table = "rel_author_org"
    table1 = "cra_organization"
    zhujian = 'org'
    set = 'name'
    id = 'org_id'
    authors = scrapy.Field()
    org = scrapy.Field()

class Author_patent(scrapy.Item):
    table = "rel_author_patent"
    table1 = "cra_patent"
    zhujian = 'patent'
    set = 'title'
    id = 'patent_id'
    authors = scrapy.Field()
    patent = scrapy.Field()